package at.ac.univie.bi.transformation.persistence;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import com.ibatis.common.jdbc.ScriptRunner;

public class DBHandler {
	
	private static DBHandler instance = null;
	
	private static String dbURLoldDB = "jdbc:derby:oldDB;create=true";
	private static String dbURLnewDB = "jdbc:derby:newDB;create=true";
	private static Connection oldDB;
	private static Connection integrationDB;
	private static Statement stmt = null;
	
	private static String integrationDBScriptPath = "./resources/newDB.sql";
	private String oldDBScriptPath = "./resources/oldDB.sql";
	
	
	private DBHandler() {
		createConnection();
		initData(oldDB, oldDBScriptPath);
		initData(integrationDB, integrationDBScriptPath);
	}
	
	public static DBHandler getInstance() {
		if (instance == null) {
			instance = new DBHandler();
		}
		
		return instance;
	}
	
	private static void createConnection()
	{
      try
      {
		oldDB = DriverManager.getConnection(dbURLoldDB);
		if (oldDB != null) {
			System.out.println("Connected to database: oldDB");
		}
		
		integrationDB = DriverManager.getConnection(dbURLnewDB);
		if (integrationDB != null) {
			System.out.println("Connected to database: integrationDB");
		}
		
      }
      catch (Exception except)
      {
          except.printStackTrace();
      }
  }
  
  private static void initData(Connection con, String pathToScript){
	  try {
			// Initialize object for ScripRunner
			ScriptRunner sr = new ScriptRunner(con, false, false);

			// Give the input file to Reader
			Reader reader = new BufferedReader(
                             new FileReader(pathToScript));

			// Exctute script
			sr.runScript(reader);
          
      } catch (Exception e) {	
			System.err.println("Failed to Execute" + pathToScript
					+ " The error is " + e.getMessage());
		} 
    } 
 
  	public static Connection getOldDB() {
		return oldDB;
	}
	
	public static void setOldDB(Connection oldDB) {
		DBHandler.oldDB = oldDB;
	}


	public static Connection getIntegrationDB() {
		return integrationDB;
	}

	public static void setIntegrationDB(Connection integrationDB) {
		DBHandler.integrationDB = integrationDB;
	}

	private static void shutdown(Connection con)
  {
      try
      {
          if (con != null)
          {
	              con.close();
	          }           
	      }
	      catch (SQLException sqlExcept)
	      {
	          
	      }
  }
  
  
 }
